﻿using Medart.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Medart.Controllers
{
    public class HomeController : Controller
    {
        private HMS909Context db = new HMS909Context();
        public ActionResult Index()
        {
            DateTime to = DateTime.Today;
            DateTime from = DateTime.Today.AddDays(-1);

            string query = "SELECT sum(TOTAL_AMOUNT_Base_2) from [HMS909].[dbo].[SO_BillHeader] where [date] = '"+to+"'";
            var total = db.Database.SqlQuery<Nullable<Double>>(query).FirstOrDefault();
            ViewData["todayFullTotal"] = total;

            query = "SELECT sum(patient_AMOUNT_Base_2) from [HMS909].[dbo].[SO_BillHeader] where [date] = '" + to + "'";
            total = db.Database.SqlQuery<Nullable<Double>>(query).FirstOrDefault();
            ViewData["todayRealTotal"] = total;
            
            
            
            query = "SELECT sum(patient_AMOUNT_Base_2) from [HMS909].[dbo].[SO_BillHeader] where [date] = '" + from + "'";
            total = db.Database.SqlQuery<Nullable<Double>>(query).FirstOrDefault();
            if(total != null)
            ViewData["yesterdayRealTotal"] = (double)total;

            query = "SELECT sum(TOTAL_AMOUNT_Base_2) from [HMS909].[dbo].[SO_BillHeader] where [date] = '" + from + "'";
            total = db.Database.SqlQuery<Nullable<Double>>(query).FirstOrDefault();

            if(total!= null)
            ViewData["yesterdayFullTotal"] = (double)total;

            query = "select count(*) from [hms909].[dbo].[schedule] where [date] = '" + to + "' and recall = 0";
            total = db.Database.SqlQuery<Nullable<int>>(query).FirstOrDefault();
            if (total != null)
            ViewData["noApts"] = total;
            query = "select count(*) from [hms909].[dbo].[schedule] where [date] = '" + from + "' and recall = 0";
            
            total = db.Database.SqlQuery<Nullable<int>>(query).FirstOrDefault();
            if (total != null)
            ViewData["noAptsYes"] = total;

            to = DateTime.Today; ;
            from = DateTime.Today.AddDays(-2);
            query = "SELECT [DOCTOR_NAME] as name  ,sum([PATIENT_AMOUNT_Base_2]) as total FROM [HMS909].[dbo].[SO_BillHeader] where [date] = '"+from+"' group   by [DOCTOR_NAME]";
            DoctorNamesCollection dc = new DoctorNamesCollection();
            var list = db.Database.SqlQuery<DoctorNames>(query);
            if (list != null)
            {
                ViewData["yesDocs"] = list;
                dc.yesterday = list;
            }
            query = "SELECT [DOCTOR_NAME] as name  ,sum([PATIENT_AMOUNT_Base_2]) as total FROM [HMS909].[dbo].[SO_BillHeader] where [date] = '" + to + "' group   by [DOCTOR_NAME]";

             list = db.Database.SqlQuery<DoctorNames>(query);
             if (list != null)
             {
                 ViewData["todayDocs"] = list;
                 dc.today = list;

             }
            
            
            
            ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";

            for (int i = 0; i > -13; i--)
            {
                //month2
                to = DateTime.Today.AddMonths(i);
                from = DateTime.Today.AddMonths(i - 1);
                query = "SELECT sum(patient_AMOUNT_Base_2) from [HMS909].[dbo].[SO_BillHeader] where [date] < '" + to + "' and [date] >= '" + from + "'";
                total = db.Database.SqlQuery<Nullable<Double>>(query).FirstOrDefault();
                ViewData["month" + Math.Abs(i)] = total;
            }

            from = DateTime.Today.AddDays(-DateTime.Today.Day+1); ;
            to = DateTime.Today; ;
            query = "SELECT [DOCTOR_NAME] as name  ,sum([PATIENT_AMOUNT_Base_2]) as total FROM [HMS909].[dbo].[SO_BillHeader] where [date] <= '" + to + "' and [date] >= '"+from+"' group   by [DOCTOR_NAME]";

            list = db.Database.SqlQuery<DoctorNames>(query);
            if (list != null)
            {
                ViewData["monthDocs"] = list;
                dc.yesterday = list;

            }

            return View(dc);
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your app description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}
